﻿<cfsilent>
	<cfscript>
		sequenceMatrixGroupUser = getProperty("serviceFactory").getBean("sequenceMatrixGroupUser");
		
		dataAdded = 0;
		dataUpdated = 0;
		
		authAdded = 0;
		authUpdated = 0;
		
		sql = "SELECT a.tch_id
				FROM t_teacher a 
				ORDER BY a.tch_id ASC";
				
		queryObj = new Query(datasource = application.dnsSlave);
		rs_teacherProfiles = queryObj.execute(sql=sql).getResult();
						
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		
		currentTS = datetimeAdvice.getCurrentDatetimeString();
		
											
		sql = "SELECT 
				a.tch_id, 
				a.tch_name, 
				a.tch_email 
				FROM t_teacher  a
				WHERE 
					NOT EXISTS (
						SELECT 1
						FROM t_user u
						WHERE
							u.user_id = a.tch_id 
					)
					OR
					NOT EXISTS (
						SELECT 1
						FROM t_groupuser ug
						WHERE
							ug.user_id = a.tch_id
							AND 
							ug.user_id = 'P001'
					)
				ORDER BY a.tch_id asc";
				
		queryObj = new Query(datasource = application.dnsSlave);
		rs_teacher = queryObj.execute(sql=sql).getResult();
		
	</cfscript>
</cfsilent>

<p class="notice">获取 <cfoutput>#rs_teacherProfiles.recordCount#</cfoutput> 条教师档案数据记录</p>

<cftry>
	<cftransaction>

		<!---
			P001 教师
		--->
		
        <cfloop query="rs_teacher" >
			
            <cfset sql = "SELECT 1
							FROM t_user a 
							WHERE a.user_id = :userId "/>
	
			<cfset queryObj = new Query(datasource = application.dnsMaster)/>
			<cfset queryObj.addParam(name="userId", value=rs_teacher.tch_id, cfsqltype="cf_sql_varchar")/>
			
			<cfset rs_user = queryObj.execute(sql=sql).getResult()/>
			
			<cfif rs_user.recordCount GT 0>

					<cfset dataUpdated ++ />

				<cfelse>

                    <cfset sql = "INSERT INTO t_user (
										 user_id,
										 user_name,
										 user_email,
										 user_last_login,
										 user_last_login_ip  
									) VALUES (
										:UID ,
										:Name ,
										:Email ,
										:LastTimeLogin ,
										:LastTimeIP 	 
									)"/>
                    <cfset queryObj = new Query(datasource = application.dnsMaster)/>
                    <cfset queryObj.addParam(name="UID", value=rs_teacher.tch_id, cfsqltype="cf_sql_varchar")/>
                    <cfset queryObj.addParam(name="Name", value=rs_teacher.tch_name, cfsqltype="cf_sql_varchar")/>
                    <cfset queryObj.addParam(name="Email", value=rs_teacher.tch_email, cfsqltype="cf_sql_varchar")/>
                    <cfset queryObj.addParam(name="LastTimeLogin", value=currentTS, cfsqltype="cf_sql_char")/>
                    <cfset queryObj.addParam(name="LastTimeIP", value='127.0.0.1', cfsqltype="cf_sql_varchar")/>
                    <cfset queryObj.execute(sql=sql)/>
                    
					<cfset dataAdded ++ />
			</cfif>
		</cfloop>
		
        <cfloop query="rs_teacher" >
			
			<!--- 创建用户授权 --->
			
            <cfset sequenceMatrixGroupUser.setUserId(rs_teacher.tch_id)/>
            <cfset sequenceMatrixGroupUser.setGroupId('P001')/>
            <cfset userGroupId = sequenceMatrixGroupUser.getID()/>
			
            <cfset sql = "SELECT 1
							FROM t_groupuser a 
							WHERE a.ugid = :userId "/>
	
			<cfset queryObj = new Query(datasource = application.dnsMaster)/>
			<cfset queryObj.addParam(name="userId", value=userGroupId, cfsqltype="cf_sql_varchar")/>
			
			<cfset rs_userGroupCheck = queryObj.execute(sql=sql).getResult()/>
			
			<cfif rs_userGroupCheck.recordCount EQ 0 >
					
                    <cfset sql = "INSERT INTO t_groupuser (
										user_id,
										group_id,
										ugid,
										date_create,
										date_lastupdate
									) VALUES (
										:MatrixUser ,
										:MatrixGroup ,
										:UserGroupID ,
										:Created ,
										:LastUpdate 
									)"/>
                                    
                    <cfset queryObj = new Query(datasource = application.dnsMaster)/>
                    <cfset queryObj.addParam(name="MatrixUser", value=rs_teacher.tch_id, cfsqltype="cf_sql_varchar")/>
                    <cfset queryObj.addParam(name="MatrixGroup", value='P001', cfsqltype="cf_sql_char")/>
                    <cfset queryObj.addParam(name="UserGroupID", value=userGroupId, cfsqltype="cf_sql_varchar")/>
                    <cfset queryObj.addParam(name="Created", value=currentTS, cfsqltype="cf_sql_char")/>
                    <cfset queryObj.addParam(name="LastUpdate", value=currentTS, cfsqltype="cf_sql_char")/>
                    <cfset queryObj.execute(sql=sql)/>
                    
					<cfset dataAdded ++ />

					<cfset authAdded ++ />
			</cfif>

		</cfloop>
	
	</cftransaction>

	<cfcatch type="any">
		<cfset hasError = true />
		<p class="warn">记录处理异常: <cfoutput>#cfcatch.Detail#</cfoutput></p>
		<p class="warn">回滚数据</p>
	</cfcatch>

</cftry>

<cfif not isDefined("hasError")>
	<p class="notice">追加数据 <cfoutput>#dataAdded#</cfoutput> 条, 更新数据 <cfoutput>#dataUpdated#</cfoutput> 条, 追加授权 <cfoutput>#authAdded#</cfoutput> 条</p>
</cfif>